﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using ET.DBCommon.Model;
using System.Collections.ObjectModel;
using MySql.Data.MySqlClient;

namespace ET.DBCommon.DAL
{
    public class TestVoiceLibraryDAL
    {
        public static bool InsertTestVoiceLibrary(out int newId, TestVoiceLibraryModel testVoice)
        {
            string sql = "insert into testvoicelibrary (`TestPaperID`,TestQuestionID,VoiceLibraryID) values (@TestPaperID,@TestQuestionID,@VoiceLibraryID)";
            MySqlParameter[] parms = {
                new MySqlParameter("@TestPaperID", testVoice.TestPaperID),
                new MySqlParameter("@TestQuestionID",testVoice.TestQuestionID),
                 new MySqlParameter("@VoiceLibraryID",testVoice.VoiceLibraryID)
            };
            return DbHelper.ExecuteNonQuery(out newId, sql, parms) > 0;
        }
        public static TestVoiceLibraryModel SelectTestVoiceLibraryInfo(int TestPaperID, int TestQuestionID,int VoiceLibraryID)
        {
            string sql = "select * from testvoicelibrary where TestPaperID=" + TestPaperID+ " and TestQuestionID="+ TestQuestionID+ " and VoiceLibraryID="+ VoiceLibraryID;
            DataSet ds = DbHelper.ExecuteDataset(sql);
            TestVoiceLibraryModel info = new TestVoiceLibraryModel ();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    info.TestPaperID = Convert.ToInt32(dt.Rows[i]["TestPaperID"].ToString());
                    info.TestQuestionID = Convert.ToInt32(dt.Rows[i]["TestQuestionID"].ToString());
                    info.VoiceLibraryID = Convert.ToInt32(dt.Rows[i]["VoiceLibraryID"].ToString());
                }
            }
            return info;
        }
        public static List<TestVoiceLibraryModel> SelectVoiceLibraryInfo(int TestPaperID, int TestQuestionID)
        {
            string sql = "select * from testvoicelibrary where TestPaperID=" + TestPaperID + " and TestQuestionID=" + TestQuestionID ;
            DataSet ds = DbHelper.ExecuteDataset(sql);
            List<TestVoiceLibraryModel> info = new List<TestVoiceLibraryModel>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    TestVoiceLibraryModel libraryModel = new TestVoiceLibraryModel();
                    libraryModel.TestPaperID = Convert.ToInt32(dt.Rows[i]["TestPaperID"].ToString());
                    libraryModel.TestQuestionID = Convert.ToInt32(dt.Rows[i]["TestQuestionID"].ToString());
                    libraryModel.VoiceLibraryID = Convert.ToInt32(dt.Rows[i]["VoiceLibraryID"].ToString());
                    info.Add(libraryModel);
                }
            }
            return info;
        }
        public static void DeleteTestVoiceLibrary(int TestPaperID, int TestQuestionID)
        {
            string sql = "delete from testvoicelibrary where TestPaperID=" + TestPaperID + " and TestQuestionID=" + TestQuestionID;
            MySqlParameter[] parms = {
                new MySqlParameter("@TestPaperID", TestPaperID),
                new MySqlParameter("@TestQuestionID", TestQuestionID)
            };
            DbHelper.ExecuteNonQuery(sql, parms);
        }
    }
}
